﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using GovernmentOfficeProject.LevelDataAccessModel;
using GovernmentOfficeProject.SysTools;
using System.Data.SqlClient;
using System.Data;
using System.Text;

namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_SYS_Button
    {
        public Entity_SYS_Button getButtonByButtonID(decimal buttonID)
        {
            //AppLog.Write(buttonID.ToString(), AppLog.LogMessageType.Debug);
            try
            {
                string strSQL = "select * from [SYS_BUTTON] where BtnID=@btnID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@btnID",buttonID)
                };
                DataSet dst = DAL_DBGM.GetDataSet(strSQL, values);
                Entity_SYS_Button button = null;
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    button = new Entity_SYS_Button();
                    button.BtnID = Convert.ToDecimal(row["BtnID"]);
                    button.BtnName = Convert.ToString(row["BtnName"]);
                    button.BtnNo = Convert.ToString(row["BtnNo"]);
                    button.BtnClass = Convert.ToString(row["BtnClass"]);
                    button.BtnScript = Convert.ToString(row["BtnScript"]);
                    button.BtnMenuNo = Convert.ToString(row["BtnMenuNo"]);
                    button.BtnInitStatus = Convert.ToDecimal(row["BtnInitStatus"]);
                    button.BtnSeqNo = Convert.ToInt32(row["BtnSeqNo"]);
                }
                return button;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Button.getButtonByButtonID(decimal buttonID)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public List<Entity_SYS_Button> getButtonList()
        {
            try
            {
                string strSQL = "select * from [SYS_BUTTON]";
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_SYS_Button button = null;
                List<Entity_SYS_Button> buttonList = new List<Entity_SYS_Button>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    button = new Entity_SYS_Button();
                    button.BtnID = Convert.ToDecimal(row["BtnID"]);
                    button.BtnName = Convert.ToString(row["BtnName"]);
                    button.BtnNo = Convert.ToString(row["BtnNo"]);
                    button.BtnClass = Convert.ToString(row["BtnClass"]);
                    button.BtnScript = Convert.ToString(row["BtnScript"]);
                    button.BtnMenuNo = Convert.ToString(row["BtnMenuNo"]);
                    button.BtnInitStatus = Convert.ToDecimal(row["BtnInitStatus"]);
                    button.BtnSeqNo = Convert.ToInt32(row["BtnSeqNo"]);
                    buttonList.Add(button);
                }
                return buttonList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Button.getButtonList()---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public List<Entity_SYS_Button> getButtonListByButtonEntity(Entity_SYS_Button buttonEntity)
        {
            try
            {
                string strSQL = "select * from [SYS_BUTTON] where 1=1";
                if (!String.IsNullOrEmpty(buttonEntity.BtnName))
                {
                    strSQL += " and BtnName='"+buttonEntity.BtnName+"'";
                }
                if (!String.IsNullOrEmpty(buttonEntity.BtnNo))
                {
                    strSQL +=" and BtnNo='"+buttonEntity.BtnNo+"'";
                }
                if (!String.IsNullOrEmpty(buttonEntity.BtnClass))
                {
                    strSQL += " and BtnClass='"+buttonEntity.BtnClass+"'";
                }
                if (!String.IsNullOrEmpty(buttonEntity.BtnIcon))
                {
                    strSQL += " and BtnIcon='"+buttonEntity.BtnIcon+"'";
                }
                if (!String.IsNullOrEmpty(buttonEntity.BtnScript))
                {
                    strSQL += " and BtnScript='" +buttonEntity.BtnScript+ "'";
                }
                if (!String.IsNullOrEmpty(buttonEntity.BtnMenuNo))
                {
                    strSQL += " and BtnMenuNo='" + buttonEntity.BtnMenuNo + "'";
                }
                if (buttonEntity.BtnInitStatus != -1)
                {
                    strSQL += " and BtnInitStatus=" + buttonEntity.BtnInitStatus;
                }
                if (buttonEntity.BtnSeqNo != -1)
                {
                    strSQL += " and BtnSeqNo=" + buttonEntity.BtnSeqNo;
                }
                //AppLog.Write("DAL_SYS_Button.getButtonList()的SQL语句:" + strSQL.ToString(), AppLog.LogMessageType.Debug);
                DataSet dst = DAL_DBGM.GetDataSet(strSQL);
                Entity_SYS_Button button = null;
                List<Entity_SYS_Button> buttonList = new List<Entity_SYS_Button>();
                foreach (DataRow row in dst.Tables[0].Rows)
                {
                    button = new Entity_SYS_Button();
                    button.BtnID = Convert.ToDecimal(row["BtnID"]);
                    button.BtnName = Convert.ToString(row["BtnName"]);
                    button.BtnNo = Convert.ToString(row["BtnNo"]);
                    button.BtnClass = Convert.ToString(row["BtnClass"]);
                    button.BtnScript = Convert.ToString(row["BtnScript"]);
                    button.BtnMenuNo = Convert.ToString(row["BtnMenuNo"]);
                    button.BtnInitStatus = Convert.ToDecimal(row["BtnInitStatus"]);
                    button.BtnSeqNo = Convert.ToInt32(row["BtnSeqNo"]);
                    buttonList.Add(button);
                }
                return buttonList;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Button.getButtonList()---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool addButton(Entity_SYS_Button buttonEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                StringBuilder sqlBudIns = new StringBuilder();
                StringBuilder sqlBudValue = new StringBuilder();
                sqlBudIns.Append("insert into [SYS_BUTTON](");
                sqlBudValue.Append(" values(");
                List<SqlParameter> sprList = new List<SqlParameter>();

                if (String.IsNullOrEmpty(buttonEntity.BtnName))
                {
                    sqlBudIns.Append("BtnName");
                    sqlBudValue.Append("@btnName,");
                    sprList.Add(new SqlParameter("@btnName", buttonEntity.BtnName));
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnNo))
                {
                    sqlBudIns.Append("BtnNo");
                    sqlBudValue.Append("@btnNo,");
                    sprList.Add(new SqlParameter("@btnNo", buttonEntity.BtnNo));
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnClass))
                {
                    sqlBudIns.Append("BtnClass");
                    sqlBudValue.Append("@btnClass,");
                    sprList.Add(new SqlParameter("@btnClass", buttonEntity.BtnClass));
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnIcon))
                {
                    sqlBudIns.Append("BtnIcon");
                    sqlBudValue.Append("@btnIcon,");
                    sprList.Add(new SqlParameter("@btnIcon", buttonEntity.BtnIcon));
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnScript))
                {
                    sqlBudIns.Append("BtnScript");
                    sqlBudValue.Append("@btnScript,");
                    sprList.Add(new SqlParameter("@btnScript", buttonEntity.BtnScript));
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnMenuNo))
                {
                    sqlBudIns.Append("BtnMenuNo");
                    sqlBudValue.Append("@btnMenuNo,");
                    sprList.Add(new SqlParameter("@btnMenuNo", buttonEntity.BtnMenuNo));
                    blState = true;
                }
                if (buttonEntity.BtnInitStatus != -1)
                {
                    sqlBudIns.Append("BtnInitStatus");
                    sqlBudValue.Append("@btnInitStatus,");
                    sprList.Add(new SqlParameter("@btnInitStatus", buttonEntity.BtnInitStatus));
                    blState = true;
                }
                if (buttonEntity.BtnSeqNo != -1)
                {
                    sqlBudIns.Append("BtnSeqNo");
                    sqlBudValue.Append("@btnSeqNo,");
                    sprList.Add(new SqlParameter("@btnSeqNo", buttonEntity.BtnSeqNo));
                    blState = true;
                }

                if (blState)
                {
                    string strSQL = sqlBudIns.ToString().Substring(0, sqlBudIns.Length - 1) + ")" + sqlBudValue.ToString().Substring(0, sqlBudValue.Length - 1) + ")";
                    SqlParameter[] values = sprList.ToArray();
                    //AppLog.Write("DAL-增加Menu的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                }
                else
                {
                    //添加Button失败:没有任何数据,无法添加.
                    AppLog.Write("添加Button失败:没有任何数据.", AppLog.LogMessageType.Info);
                }
                return flag;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Button.addButton(Entity_SYS_Button buttonEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool updateButtonByButtonIDAndButtonEntity(decimal buttonID, Entity_SYS_Button buttonEntity)
        {
            try
            {
                bool flag = false;
                bool blState = false;
                List<SqlParameter> sprList = new List<SqlParameter>();
                string strSQL = "update [SYS_BUTTON] set";
                if (String.IsNullOrEmpty(buttonEntity.BtnName))
                {
                    strSQL += " BtnName='" + buttonEntity.BtnName + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnNo))
                {
                    strSQL += " BtnNo='" + buttonEntity.BtnNo + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnClass))
                {
                    strSQL += " BtnClass='" + buttonEntity.BtnClass + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnIcon))
                {
                    strSQL += " BtnIcon='" + buttonEntity.BtnIcon + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnScript))
                {
                    strSQL += " BtnScript='" + buttonEntity.BtnScript + "',";
                    blState = true;
                }
                if (String.IsNullOrEmpty(buttonEntity.BtnMenuNo))
                {
                    strSQL += " BtnMenuNo='" + buttonEntity.BtnMenuNo + "',";
                    blState = true;
                }
                if (buttonEntity.BtnInitStatus != -1)
                {
                    strSQL += " BtnInitStatus=" + buttonEntity.BtnInitStatus + "'";
                    blState = true;
                }
                if (buttonEntity.BtnSeqNo != -1)
                {
                    strSQL += " BtnSeqNo=" + buttonEntity.BtnSeqNo + "'";
                    blState = true;
                }

                if (blState)
                {                   
                    strSQL = strSQL.Substring(0, strSQL.Length - 1) + " where BtnID=@buttonID";
                    SqlParameter[] values = new SqlParameter[]{
                        new SqlParameter("@buttonID",buttonID)
                    };
                    //AppLog.Write("DAL-修改Button的SQL语句: " + strSQL, AppLog.LogMessageType.Debug);
                    flag = DAL_DBGM.InsertInfo(strSQL, values);
                }
                else
                {
                    //添加Button失败:没有任何数据,无法添加.
                    AppLog.Write("添加Button失败:没有任何数据.", AppLog.LogMessageType.Info);
                }
                return flag;
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Button.updateButtonByButtonIDAndButtonEntity(decimal buttonID, Entity_SYS_Button buttonEntity)---", AppLog.LogMessageType.Error);
                throw ex;
            }
        }

        public bool deleteButtonByButtonID(decimal buttonID)
        {
            try
            {
                string strSQL = "delete from [SYS_BUTTON] where BtnID=@buttonID";
                SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@buttonID",buttonID)
                };
                return DAL_DBGM.DeleteInfo(strSQL,values);
            }
            catch (System.Exception ex)
            {
                AppLog.Write("---DAL_SYS_Button.deleteButtonByButtonID(decimal buttonID)---", AppLog.LogMessageType.Error);
                throw ex;    
            }
        }


        #region 用户权限相关

        /// <summary>
        /// 获得用户权限的所有菜单按钮
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public List<Entity_SYS_Button> getButtonListByMenu(decimal userId, string menuNo)
        {
            string sql = @" SELECT * FROM SYS_BUTTON M WHERE(BtnID IN({0}) OR BtnID IN({1})) AND BtnID NOT IN ({2}) AND BtnMenuNo = '{3}'";

            //子查询1：获取用户有权限的按钮ID
            string query1 = @"SELECT DISTINCT P.PriAccessValue FROM SYS_PRIVILEGE P WHERE P.PriMaster = 'PER_USER' AND P.PriMasterValue = {0} AND P.PriAccess = 'SYS_BUTTON' AND P.PriOperation = 'Permit'".FormatWith(userId);
            //子查询2：获取用户所属角色有权限的按钮ID
            string query2 = @"SELECT P.PriAccessValue FROM SYS_PRIVILEGE P WHERE P.PriMaster = 'PER_ROLE' AND P.PriMasterValue IN  (SELECT M.URRoleID FROM PER_USER_ROLE M WHERE M.URUserID = {0}) AND P.PriAccess = 'SYS_BUTTON' AND P.PriOperation = 'Permit'".FormatWith(userId);
            //子查询3：获取用户禁止权限的按钮ID
            string query3 = @"SELECT DISTINCT P.PriAccessValue FROM SYS_PRIVILEGE P WHERE P.PriMaster = 'PER_USER' AND P.PriMasterValue = {0} AND P.PriAccess = 'SYS_BUTTON' AND P.PriOperation = 'Forbid'".FormatWith(userId);

            sql = sql.FormatWith(query1, query2, query3,menuNo);
            List<Entity_SYS_Button> list = null;
            try
            {
                list = getButtonListBySql(sql);
            }
            catch (System.Exception ex)
            {
                AppLog.Write(ex.Message, AppLog.LogMessageType.Error);
                throw ex;
            }
            return list;
        }

        /// <summary>
        /// 获得关联用户权限的所有菜单按钮
        /// </summary>
        /// <param name="masterUserId"></param>
        /// <param name="controlledUserId"></param>
        /// <returns></returns>
        public List<Entity_SYS_Button> getButtonListByRelation(decimal masterUserId,decimal controlledUserId)
        {
            string sql = @"select * from SYS_BUTTON T WHERE T.BtnID IN ({0})";

            //获取关联用户所具有的权限按钮ID
            string query1 = @"SELECT DISTINCT P.PriAccessValue FROM SYS_PRIVILEGE P WHERE P.PriID IN ({0})";
            string query2 = @"SELECT R.URControlledPrivilegeID FROM PER_USER_RELATION R WHERE R.URMasterUserID = {0} AND R.URControlledUserID = {1}".FormatWith(masterUserId, controlledUserId);
            sql = sql.FormatWith(query1.FormatWith(query2));
            List<Entity_SYS_Button> list = null;
            try
            {
                list = getButtonListBySql(sql);
            }
            catch (System.Exception ex)
            {
                AppLog.Write(ex.Message, AppLog.LogMessageType.Error);
                throw ex;
            }
            return list;
        }

        #endregion

        private List<Entity_SYS_Button> getButtonListBySql(string sql)
        {
            DataSet dst = DAL_DBGM.GetDataSet(sql);
            Entity_SYS_Button button = null;
            List<Entity_SYS_Button> buttonList = new List<Entity_SYS_Button>(); ;
            foreach (DataRow row in dst.Tables[0].Rows)
            {
                button = new Entity_SYS_Button();
                button.BtnID = Convert.ToDecimal(row["BtnID"]);
                button.BtnName = Convert.ToString(row["BtnName"]);
                button.BtnNo = Convert.ToString(row["BtnNo"]);
                button.BtnClass = Convert.ToString(row["BtnClass"]);
                button.BtnScript = Convert.ToString(row["BtnScript"]);
                button.BtnMenuNo = Convert.ToString(row["BtnMenuNo"]);
                button.BtnInitStatus = Convert.ToDecimal(row["BtnInitStatus"]);
                button.BtnSeqNo = Convert.ToInt32(row["BtnSeqNo"]);
                buttonList.Add(button);
            }
            return buttonList;
        }
        
    }
}
